﻿// MIT License
//
// Copyright (c) 2021-present zuohuaijun, Daming Co.,Ltd and Contributors
//
// 电话/微信：18020030720 QQ群1：87333204 QQ群2：252381476

using Admin.NET.Application.Const;
using Admin.Net.InformationPlatform.Entity;
using Furion.DependencyInjection;
using Furion.DynamicApiController;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Admin.Net.InformationPlatform.Entity.Inventory;
using Microsoft.AspNetCore.Mvc;
using Furion.FriendlyException;
using Admin.Net.InformationPlatform.Entity.Equipment;
using Admin.Net.InformationPlatform.Service.Inventory.Dto;
using Microsoft.Office.Interop.Word;
using Admin.NET.Core;
using Admin.Net.InformationPlatform.Service.Equipment.Dto;
using Microsoft.AspNetCore.Http;
using System.Security.Cryptography;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using Admin.Net.InformationPlatform.Service.Order.Dto;
using NPOI.Util;
using Admin.NET.Core.Entity;
using Aliyun.Acs.Core.Exceptions;
using Aliyun.Acs.Core.Profile;
using Aliyun.Acs.Core;
using Aliyun.Acs.Dysmsapi.Model.V20170525;
using Microsoft.AspNetCore.Authorization;
using Castle.Components.DictionaryAdapter.Xml;
using static SKIT.FlurlHttpClient.Wechat.Api.Models.CgibinMessageCustomSendRequest.Types;
using Nest;
using MathNet.Numerics.Distributions;
using SjEntityClass;

namespace Admin.Net.InformationPlatform.Service.Inventory;

/// <summary>
/// ME领用库存预警
/// </summary>
[ApiDescriptionSettings(groups: "Inventory", Name = "ME领用库存预警系统", Tag = "库存管理", Order = 200)]
[Route("Inventory/")]
public class InventoryForewarnService : IDynamicApiController, ITransient
{
    /// <summary>
    /// 单例数据链接
    /// </summary>
    ISqlSugarClient db;
    /// <summary>
    /// T100数据库
    /// </summary>
    private SqlSugarProvider _T100Db;
    private SqlSugarProvider _SjDb;
    private SqlSugarProvider _plm;
    private SqlSugarProvider _Dilon;
   // private readonly SqlSugarRepository<SysPhoneCode> _sysPhoneCode;


    /// <summary>
    /// 用户信息
    /// </summary>
    TokenUser _User;
    /// <summary>
    /// SOP工程文件管理
    /// </summary>
    /// <param name="db"></param>
    public InventoryForewarnService(ISqlSugarClient db)
    {
        this.db = db;
        this._T100Db = this.db.AsTenant().GetConnection(T100Area.ConfigId);
        this._SjDb = this.db.AsTenant().GetConnection(SJDB.ConfigId);
        this._plm = this.db.AsTenant().GetConnection(PLM.ConfigId);
        this._Dilon = this.db.AsTenant().GetConnection(Dilon.ConfigId);
        // 获取 `Jwt` 存储的信息 企业编号与据点信息
        _User = Util.SjCommonUtil.GetT100UserTokenInfo(_T100Db, _Dilon);
       // _sysPhoneCode = sysPhoneCode;

    }

    /// <summary>
    /// 添加ME库存信息
    /// </summary>
    /// <returns></returns>
    [HttpPost("/InsertInventoryForewarnMessage")]
    public async Task<string> InsertInventoryForewarnMessage([FromBody] List<InsertInventoryInput> inputList) {

        var LoginRoles = _Dilon.Queryable<SysUserRole>()
             .LeftJoin<SysUser>((r, u) => (r.UserId == u.Id))
                   .Where((r, u) => r.UserId.ToString() == _User.ID)
                  .Select((r, u) => new SalesmanRole
                  {
                      USERID = u.Account,
                      USERNAME = u.RealName,
                      ROLEID = r.RoleId.ToString()
                  }).ToList();

        int role = LoginRoles.Where(u => u.ROLEID == "14930050012997").Count();//判断是否拥有ME库存管理员角色

        if (role == 0)
        {
            throw Oops.Oh($"您这边没有权限！");

        }

        List<InventoryForewarn> inventoryList = new List<InventoryForewarn>();

        DateTime time = DateTime.Now;

        if (inputList.Count == 0) {

            throw Oops.Oh($"添加信息不能为0条！");

        }

        foreach (InsertInventoryInput input in inputList)
        {
            if (string.IsNullOrEmpty(input.PARTNUMBER) || string.IsNullOrEmpty(input.SPECIFICATIONTYPE) ||
                string.IsNullOrEmpty(input.USEDEPARTMENT) || string.IsNullOrEmpty(input.PARTSNAME)) {

                throw Oops.Oh($"传入数据信息不完整!");


            }
            else {

               int t100Count =  _T100Db.Queryable<ImaaT>()
                    .Where(a => a.Imaa001 == input.PARTNUMBER)
                    .Count();

                if (t100Count ==0) {
                    throw Oops.Oh($"该料号在T100中不存在！");

                }

                //判断单据存在
                int cnt = _SjDb.Queryable<InventoryForewarn>()
                    .Where(a => a.PARTNUMBER == input.PARTNUMBER)
                     .Where(l => l.PARTSNAME == input.PARTSNAME)
                      .Where(l => l.SPECIFICATIONTYPE == input.SPECIFICATIONTYPE)
                      .Count();

                if (cnt > 0)
                {
                    throw Oops.Oh($"任务已经存在！");
                }


                InventoryForewarn inventory = new InventoryForewarn() {
                    PARTNUMBER = input.PARTNUMBER,
                    SPECIFICATIONTYPE = input.SPECIFICATIONTYPE,
                    PARTSNAME = input.PARTSNAME,
                    USEDEPARTMENT = input.USEDEPARTMENT,
                    SAFEINVENTORY = input.SAFEINVENTORY,
                    MAXIMUMINVENTORY = input.MAXIMUMINVENTORY,
                    //NOWINVENTORY = input.NOWINVENTORY,
                    EQUIPMENTAMOUNT = input.EQUIPMENTAMOUNT,
                    ESTIMATEAMOUNT = input.ESTIMATEAMOUNT,
                   // PROCUREMENTAMOUNT = (input.SAFEINVENTORY - input.NOWINVENTORY) * input.ESTIMATEAMOUNT,
                    PROCUREMENTCYCLE = input.PROCUREMENTCYCLE,
                    USAGECYCLE = input.USAGECYCLE,
                    MANUFACTURERS = input.MANUFACTURERS,
                    INVENTORYREMARK = input.INVENTORYREMARK,
                    CREATETIME = time,

                };

                inventoryList.Add(inventory);

            }

        }

        #region 插入事务
        try
        {
            //开启事务
            _SjDb.Ado.BeginTran();
            int cnt = await _SjDb.Insertable<InventoryForewarn>(inventoryList).ExecuteCommandAsync();

            if (cnt == 0)
            {
                throw Oops.Oh($"新建任务失败！");
            }
            //提交事务
            _SjDb.Ado.CommitTran();
            return "插入成功";
        }
        catch (Exception e)
        {
            //回滚事务
            _SjDb.Ado.RollbackTran();
            throw Oops.Oh($"异常：{e.Message}");

        }
        #endregion

    }

    /// <summary>
    /// 查询ME库存信息
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    [HttpPost("/SelectInventoryForewarnMessage")]
    public async Task<SqlSugarPagedList<InventoryForewarn>> SelectInventoryForewarnMessage(SelectInventoryInput input) {


     /*   List<string> partNumberList =  _SjDb.Queryable<InventoryForewarn>()
         .WhereIF(!string.IsNullOrEmpty(input.PARTNUMBER), (l) => (l.PARTNUMBER.Contains(input.PARTNUMBER)))//品号
         .WhereIF(!string.IsNullOrEmpty(input.PARTSNAME), (l) => (l.PARTSNAME.Contains(input.PARTSNAME)))//备件名称
         .WhereIF(!string.IsNullOrEmpty(input.SPECIFICATIONTYPE), (l) => (l.SPECIFICATIONTYPE.Contains(input.SPECIFICATIONTYPE)))//规格型号
         .WhereIF(!string.IsNullOrEmpty(input.USEDEPARTMENT), (l) => (l.USEDEPARTMENT.Contains(input.USEDEPARTMENT)))//使用部门
         .WhereIF(!string.IsNullOrEmpty(input.MANUFACTURERS), (l) => (l.MANUFACTURERS.Contains(input.MANUFACTURERS)))//厂商
         .OrderBy(l => l.CREATETIME, OrderByType.Desc) //创建日期排序
         .Select(a => a.PARTNUMBER)
       
         .ToList();*/


        var output = await _SjDb.Queryable<InventoryForewarn>()
         .WhereIF(!string.IsNullOrEmpty(input.PARTNUMBER), (l) => (l.PARTNUMBER.Contains(input.PARTNUMBER)))//品号
         .WhereIF(!string.IsNullOrEmpty(input.PARTSNAME), (l) => (l.PARTSNAME.Contains(input.PARTSNAME)))//备件名称
         .WhereIF(!string.IsNullOrEmpty(input.SPECIFICATIONTYPE), (l) => (l.SPECIFICATIONTYPE.Contains(input.SPECIFICATIONTYPE)))//规格型号
         .WhereIF(!string.IsNullOrEmpty(input.USEDEPARTMENT), (l) => (l.USEDEPARTMENT.Contains(input.USEDEPARTMENT)))//使用部门
         .WhereIF(!string.IsNullOrEmpty(input.MANUFACTURERS), (l) => (l.MANUFACTURERS.Contains(input.MANUFACTURERS)))//厂商
         .OrderBy(l => l.CREATETIME, OrderByType.Desc) //创建日期排序
         .ToPagedListAsync(input.Page, input.PageSize);

          List<InventoryForewarn>   list =  (List< InventoryForewarn >) output.Items;

        HashSet<string> partNumberList = new HashSet<string>();
        foreach (var item in list)
        {
            partNumberList.Add(item.PARTNUMBER);
        }

        var imfaList = _T100Db.Queryable<ImaaT>()
         .LeftJoin<InagT>((a, o) => a.Imaaent == o.Inagent && a.Imaa001 == o.Inag001 && o.Inag004 == "81" && o.Inagsite == "SJGS" && o.Inag008 != 0)
         .Where(a => a.Imaaud003 == "SJGS")
         .Where(a => a.Imaastus == "Y")
         .Where(a => partNumberList.Contains(a.Imaa001))
         .Select((a, o) => new InventoryNumber()
         {
             PARTNUMBER = a.Imaa001,
             NOWINVENTORY = o.Inag009 == null ? 0 : Convert.ToInt32(o.Inag009)
         })
         .ToList();


        var result = from o in list
                     join c in imfaList on o.PARTNUMBER equals c.PARTNUMBER into PARTNUMBER
                     from c in PARTNUMBER.DefaultIfEmpty()
                     select new InventoryForewarn
                     {
                        ID = o.ID,
                         PARTNUMBER = o.PARTNUMBER,
                         PARTSNAME = o.PARTSNAME,
                         SPECIFICATIONTYPE = o.SPECIFICATIONTYPE,
                         USEDEPARTMENT =o.USEDEPARTMENT,
                         SAFEINVENTORY = o.SAFEINVENTORY,
                         MAXIMUMINVENTORY =o.MAXIMUMINVENTORY,
                         NOWINVENTORY =c.NOWINVENTORY,
                         EQUIPMENTAMOUNT =o.EQUIPMENTAMOUNT,
                         ESTIMATEAMOUNT = o.ESTIMATEAMOUNT,
                         PROCUREMENTAMOUNT =(o.SAFEINVENTORY - c.NOWINVENTORY)*o.ESTIMATEAMOUNT,  
                         PROCUREMENTCYCLE =o.PROCUREMENTCYCLE,
                         USAGECYCLE =o.USAGECYCLE,
                         MANUFACTURERS =o.MANUFACTURERS,
                         INVENTORYREMARK =o.INVENTORYREMARK,
                         CREATETIME =o.CREATETIME,

                     };


        output.Items = result;

        return output;
    }

    /// <summary>
    /// 修改ME库存信息
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    [HttpPost("/UpdateInventoryForewarnMessage")]
    public async Task<string> UpdateInventoryForewarnMessage(UpdateInventoryInput input) {


        var LoginRoles = _Dilon.Queryable<SysUserRole>()
               .LeftJoin<SysUser>((r, u) => (r.UserId == u.Id))
                     .Where((r, u) => r.UserId.ToString() == _User.ID)
                    .Select((r, u) => new SalesmanRole
                    {
                        USERID = u.Account,
                        USERNAME = u.RealName,
                        ROLEID = r.RoleId.ToString()
                    }).ToList();

        int role = LoginRoles.Where(u => u.ROLEID == "14930050012997").Count();//判断是否拥有ME库存管理员角色

        if (role ==0) {
            throw Oops.Oh($"您这边没有权限！");

        }

        if ( string.IsNullOrEmpty(input.SPECIFICATIONTYPE) || string.IsNullOrEmpty(input.USEDEPARTMENT) || string.IsNullOrEmpty(input.PARTSNAME))
        {
            throw Oops.Oh($"请填写完整参数！");
        }
        DateTime dateTime = DateTime.Now;

        //判断单据存在

        int cnt = _SjDb.Queryable<InventoryForewarn>()
                     .Where(l => l.ID == input.ID)
                     .Count();

        if (cnt == 0)
        {
            throw Oops.Oh($"任务不存在！");
        }
        else
        {
          /* double price = (input.SAFEINVENTORY - input.NOWINVENTORY) * input.ESTIMATEAMOUNT;*/
            //提交
            try
            {
                _SjDb.Ado.BeginTran();
                int count = await _SjDb.Updateable<InventoryForewarn>()
                     .SetColumns(a => a.PARTSNAME == input.PARTSNAME)
                     .SetColumns(a => a.SPECIFICATIONTYPE == input.SPECIFICATIONTYPE)
                     .SetColumns(a => a.USEDEPARTMENT == input.USEDEPARTMENT)
                     .SetColumns(a => a.SAFEINVENTORY == input.SAFEINVENTORY)
                     .SetColumns(a => a.MAXIMUMINVENTORY == input.MAXIMUMINVENTORY)
                     //.SetColumns(a => a.NOWINVENTORY == input.NOWINVENTORY)
                     .SetColumns(a => a.EQUIPMENTAMOUNT == input.EQUIPMENTAMOUNT)
                     .SetColumns(a => a.ESTIMATEAMOUNT == input.ESTIMATEAMOUNT)
                     .SetColumns(a => a.PROCUREMENTCYCLE == input.PROCUREMENTCYCLE)
                     .SetColumns(a => a.USAGECYCLE == input.USAGECYCLE)
                     .SetColumns(a => a.MANUFACTURERS == input.MANUFACTURERS)
                     .SetColumns(a => a.INVENTORYREMARK == input.INVENTORYREMARK)
                    // .SetColumns(a => a.PROCUREMENTAMOUNT == price)
                     .Where(a => a.ID == input.ID)
                    .ExecuteCommandAsync();



                if (count != 1)
                {
                    throw Oops.Oh("修改失败！");
                }

                /*//不能修改和已经拥有数据一样
                int cut2 = _SjDb.Queryable<InventoryForewarn>()
                   .Where(l => l.PARTNUMBER == input.PARTNUMBER)
                    .Where(l => l.PARTSNAME == input.PARTSNAME)
                     .Where(l => l.SPECIFICATIONTYPE == input.SPECIFICATIONTYPE)
                   .Count();


                if (cut2>1) {

                    throw Oops.Oh("数据修改后的数据重复！");
                }*/

                _SjDb.Ado.CommitTran();
                return "修改成功！";
            }
            catch (Exception e)
            {
                _SjDb.Ado.RollbackTran();
                throw Oops.Oh($"异常：{e.Message}");
            }

        }
    }

    /// <summary>
    /// 导入excel文件
    /// </summary>
    /// <param name="file"></param>
    /// <returns></returns>
    [HttpPost("/ImportsExcel")]
    public async Task<ActionResult<dynamic>> ImportsExcel(IFormFile file) {


        var LoginRoles = _Dilon.Queryable<SysUserRole>()
               .LeftJoin<SysUser>((r, u) => (r.UserId == u.Id))
                     .Where((r, u) => r.UserId.ToString() == _User.ID)
                    .Select((r, u) => new SalesmanRole
                    {
                        USERID = u.Account,
                        USERNAME = u.RealName,
                        ROLEID = r.RoleId.ToString()
                    }).ToList();

        int role = LoginRoles.Where(u => u.ROLEID == "14930050012997").Count();//判断是否拥有ME库存管理员角色

        if (role == 0)
        {
            throw Oops.Oh($"您这边没有权限！");

        }

        //判断是否选择文件
        if (file == null || file.Length <= 0)
        {
            return new { success = false, msg = "请选择导入文件！", result = "" };
        }
        //拼接出文件保存到的路径
        var path = Path.Combine(Directory.GetCurrentDirectory(), "File", "InventoryExcel");
        //判断文件后缀
        string Express = Path.GetExtension(file.FileName);
        if (Express != ".xlsx" && Express != ".xls")
        {
            throw Oops.Oh("请上传文件扩展名格式为'.xlsx'或'.xls'的文件!");
            //return BadRequest("请上传文件扩展名格式为'.xlsx'的文件!");
        }
        // 文件名md5
        string filename = Path.GetFileNameWithoutExtension(file.FileName) + '-' + Md5FileHash(file.OpenReadStream());
        //文件名+尾缀
        filename += Express;
        //检查是否存在文件夹
        if (false == System.IO.Directory.Exists(path))
        {
            //创建文件夹
            System.IO.Directory.CreateDirectory(path);
        }
        string filePath = path + $@"\{filename}";
        //保存文件
        using (FileStream fs = System.IO.File.Create(filePath))
        {
            file.CopyTo(fs);
            fs.Flush();
        }

        //获取指定地址的文件
        using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite))
        {
            // 定义一个用于存储excel表数据的集合
            // 以下模拟存储的数据集合对应实体类为T，属性有Name、Age和CreateTime
            List<InventoryForewarn> data = new List<InventoryForewarn>();
            List<InventoryForewarn> updata = new List<InventoryForewarn>();

            DateTime date = DateTime.Now;
            // 创建工作簿
            IWorkbook workbook = null;
            try
            {
                // .xlsx格式：XSSFWorkBook
                if (filename.IndexOf(".xlsx", StringComparison.Ordinal) > 0)
                {
                    workbook = new XSSFWorkbook(fs);
                }
                // .xls格式：HSSFWorkBook
                else if (filename.IndexOf(".xls", StringComparison.Ordinal) > 0)
                {
                    workbook = new HSSFWorkbook(fs);
                }
                // 创建一个 FormulaEvaluator 对象
                var evaluator = workbook.GetCreationHelper().CreateFormulaEvaluator();

                // workbook不为空(文件格式正确)才继续执行
                if (workbook != null)
                {
                    // 读取第一个sheet(表)
                    ISheet sheet = workbook.GetSheetAt(0);
                    int rowCount = sheet.LastRowNum;// 获取最后一行的索引值（总行数）
                                                    // 确定表中一定有数据再继续执行
                    if (rowCount > 0) //a <= rowCount
                    {

                        // 循环填入数据
                        // FirstRowNum 第一个逻辑行
                        for (int a = sheet.FirstRowNum + 1; a <= rowCount; a++)
                        {
                            IRow row = sheet.GetRow(a);
                            if (row != null)
                            {
                                var time = DateTime.Now;
                                string PARTNUMBER = GetCellTextValue(row, 0).Trim();
                                string PARTSNAME = GetCellTextValue(row, 1).Trim();
                                string SPECIFICATIONTYPE = GetCellTextValue(row, 2);
                                string USEDEPARTMENT = GetCellTextValue(row, 3);
                                double SAFEINVENTORY = Convert.ToDouble(string.IsNullOrWhiteSpace(GetCellTextValue(row, 4)) ? "0" : GetCellTextValue(row, 4));
                                int MAXIMUMINVENTORY = Convert.ToInt32(string.IsNullOrWhiteSpace(GetCellTextValue(row, 5)) ? "0" : GetCellTextValue(row, 5));
                                int NOWINVENTORY = Convert.ToInt32(string.IsNullOrWhiteSpace(GetCellTextValue(row, 6)) ? "0" : GetCellTextValue(row, 6));
                                string EQUIPMENTAMOUNT = GetCellTextValue(row, 7);
                                double ESTIMATEAMOUNT = Convert.ToDouble(string.IsNullOrWhiteSpace(GetCellTextValue(row, 8)) ? "0.0" : GetCellTextValue(row, 8));
                                double PROCUREMENTAMOUNT = Convert.ToDouble(string.IsNullOrWhiteSpace(GetCellTextValue(row, 9)) ? "0.0" : GetCellTextValue(row, 9));
                                int PROCUREMENTCYCLE = Convert.ToInt32(string.IsNullOrWhiteSpace(GetCellTextValue(row, 10)) ? "0" : GetCellTextValue(row, 10));
                                string USAGECYCLE = GetCellTextValue(row, 11);
                                string MANUFACTURERS = GetCellTextValue(row, 12);
                                string INVENTORYREMARK = GetCellTextValue(row, 13);

                                //判断单据存在
                                var inventories = _SjDb.Queryable<InventoryForewarn>()
                                   .Where(l => l.PARTNUMBER == PARTNUMBER)
                                    .Where(l => l.PARTSNAME == PARTSNAME)
                                     .Where(l => l.SPECIFICATIONTYPE == SPECIFICATIONTYPE)
                                   .ToList();

                                int t100Count = _T100Db.Queryable<ImaaT>()
                                .Where(a => a.Imaa001 == PARTNUMBER)
                                .Count();

                                if (t100Count!=0) {
                                    try
                                    {
                                        _SjDb.Ado.BeginTran();
                                        if (inventories.Count == 0)//新增数据
                                        {
                                            InventoryForewarn inventory = new InventoryForewarn()
                                            {
                                                PARTNUMBER = PARTNUMBER,
                                                PARTSNAME = PARTSNAME,
                                                SPECIFICATIONTYPE = SPECIFICATIONTYPE,
                                                USEDEPARTMENT = GetCellTextValue(row, 3),
                                                SAFEINVENTORY = Convert.ToDouble(string.IsNullOrWhiteSpace(GetCellTextValue(row, 4)) ? "0" : GetCellTextValue(row, 4)),
                                                MAXIMUMINVENTORY = Convert.ToInt32(string.IsNullOrWhiteSpace(GetCellTextValue(row, 5)) ? "0" : GetCellTextValue(row, 5)),
                                                //NOWINVENTORY = Convert.ToInt32(string.IsNullOrWhiteSpace(GetCellTextValue(row, 6)) ? "0" : GetCellTextValue(row, 6)),
                                                EQUIPMENTAMOUNT = GetCellTextValue(row, 7),
                                                //  ESTIMATEAMOUNT = Convert.ToDouble(string.IsNullOrWhiteSpace(GetCellTextValue(row, 8)) ? "0.0" : GetCellTextValue(row, 8)),
                                                PROCUREMENTAMOUNT = Convert.ToDouble(string.IsNullOrWhiteSpace(GetCellTextValue(row, 9)) ? "0.0" : GetCellTextValue(row, 9)),
                                                PROCUREMENTCYCLE = Convert.ToInt32(string.IsNullOrWhiteSpace(GetCellTextValue(row, 10)) ? "0" : GetCellTextValue(row, 10)),
                                                USAGECYCLE = GetCellTextValue(row, 11),
                                                MANUFACTURERS = GetCellTextValue(row, 12),
                                                INVENTORYREMARK = GetCellTextValue(row, 13),
                                                CREATETIME = date,
                                            };


                                            data.Add(inventory);
                                        }
                                        else
                                        {
                                            //批量修改
                                            int count1 = await _SjDb.Updateable<InventoryForewarn>()
                                             .SetColumns(a => a.USEDEPARTMENT == GetCellTextValue(row, 3))
                                             .SetColumns(a => a.SAFEINVENTORY == Convert.ToDouble(string.IsNullOrWhiteSpace(GetCellTextValue(row, 4)) ? "0" : GetCellTextValue(row, 4)))
                                             .SetColumns(a => a.MAXIMUMINVENTORY == Convert.ToInt32(string.IsNullOrWhiteSpace(GetCellTextValue(row, 5)) ? "0" : GetCellTextValue(row, 5)))
                                             //.SetColumns(a => a.NOWINVENTORY == Convert.ToInt32(string.IsNullOrWhiteSpace(GetCellTextValue(row, 6)) ? "0" : GetCellTextValue(row, 6)))
                                             .SetColumns(a => a.EQUIPMENTAMOUNT == GetCellTextValue(row, 7))
                                             // .SetColumns(a => a.ESTIMATEAMOUNT == Convert.ToDouble(string.IsNullOrWhiteSpace(GetCellTextValue(row, 9)) ? "0.0" : GetCellTextValue(row, 9)))
                                             .SetColumns(a => a.PROCUREMENTAMOUNT == Convert.ToDouble(string.IsNullOrWhiteSpace(GetCellTextValue(row, 9)) ? "0.0" : GetCellTextValue(row, 9)))
                                             .SetColumns(a => a.PROCUREMENTCYCLE == Convert.ToInt32(string.IsNullOrWhiteSpace(GetCellTextValue(row, 10)) ? "0" : GetCellTextValue(row, 10)))
                                             .SetColumns(a => a.USAGECYCLE == GetCellTextValue(row, 11))
                                             .SetColumns(a => a.MANUFACTURERS == GetCellTextValue(row, 12))
                                             .SetColumns(a => a.INVENTORYREMARK == GetCellTextValue(row, 13))

                                             .Where(a => a.ID == inventories[0].ID)
                                             .ExecuteCommandAsync();
                                        }
                                        _SjDb.Ado.CommitTran();
                                    }
                                    catch (Exception e)
                                    {
                                        _SjDb.Ado.RollbackTran();
                                        throw Oops.Oh($"异常：{e.Message}");
                                    }

                                }
                               


                            }
                        }
                        // 与数据库交互
                        int cnt = await _SjDb.Insertable(data).ExecuteCommandAsync();
                        return "导入成功";
                    }
                    else
                    {
                        return "表中没有数据"; ;
                    }
                }
                else
                {
                    return "导入文件格式有误";
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }



        
    }


    /// <summary>
    /// 某文件的md5值
    /// </summary>
    /// <param name="stream">file stream</param>
    /// <returns></returns>
    private string Md5FileHash(Stream stream)
    {
        using var md5 = MD5.Create();
        var data = md5.ComputeHash(stream);
        var sBuilder = new StringBuilder();
        for (var i = 0; i < data.Length; i++)
        {
            sBuilder.Append(data[i].ToString("x2"));
        }
        return sBuilder.ToString();
    }


    private string GetCellTextValue(IRow row, int column)
    {
        var cell = row.GetCell(column, MissingCellPolicy.RETURN_BLANK_AS_NULL);

        if (cell != null)
        {
            switch (cell.CellType)
            {
                case CellType.String:
                    return cell.StringCellValue;

                case CellType.Numeric:
                    if (DateUtil.IsCellDateFormatted(cell))
                    {
                        return cell.DateCellValue.ToString();
                    }
                    else
                    {
                        return cell.NumericCellValue.ToString();
                    }

                case CellType.Boolean:
                    return cell.BooleanCellValue.ToString();

                case CellType.Formula:
                    switch (cell.CachedFormulaResultType)
                    {
                        case CellType.String:
                            return cell.StringCellValue;

                        case CellType.Numeric:
                            if (DateUtil.IsCellDateFormatted(cell))
                            {
                                return cell.DateCellValue.ToString();
                            }
                            else
                            {
                                return cell.NumericCellValue.ToDouble().ToString("0.00");
                            }

                        case CellType.Boolean:
                            return cell.BooleanCellValue.ToString();

                        default:
                            return string.Empty;
                    }

                default:
                    return string.Empty;
            }
        }
        return string.Empty;
    }


    /// <summary>
    /// 创建下拉框
    ///  类型 1.使用部门 2.厂商
    /// </summary>
    /// <returns></returns>
    [HttpPost("/CreateMeInventoryOptions")]
    public async Task<string> CreateMeInventoryOptions(List<CreateMeInventoryOptionsInput> inputs) {

        List<MeInventoryOptions> list = new List<MeInventoryOptions>();

        if (inputs.Count ==0) {

            throw Oops.Oh($"添加下拉框内容不能为0条！");          

        }

        foreach (CreateMeInventoryOptionsInput input in inputs)
        {
            
            int cnt = _SjDb.Queryable<MeInventoryOptions>()
                     .Where(a => a.OPTIONS_TYPE == input.OPTIONS_TYPE)
                    .Max(it => it.OPTIONS_ID);

          int count = _SjDb.Queryable<MeInventoryOptions>()
             .Where(a => a.OPTIONS_TYPE == input.OPTIONS_TYPE)
              .Where(a => a.OPTIONS_NAME == input.OPTIONS_NAME)
             .Count();

            if (count>0) {
                throw Oops.Oh($"添加下拉框内容已存在！");
            }

            MeInventoryOptions meInventoryOptions = new MeInventoryOptions() {
                OPTIONS_TYPE = input.OPTIONS_TYPE,
                OPTIONS_NAME = input.OPTIONS_NAME,
                OPTIONS_ID = cnt + 1,

            };
            list.Add(meInventoryOptions);


        }

        #region 插入事务
        try
        {
            //开启事务
            _SjDb.Ado.BeginTran();
            int cnt = await _SjDb.Insertable<MeInventoryOptions>(list).ExecuteCommandAsync();

            if (cnt == 0)
            {
                throw Oops.Oh($"新建任务失败！");
            }
            //提交事务
            _SjDb.Ado.CommitTran();
            return "插入成功";
        }
        catch (Exception e)
        {
            //回滚事务
            _SjDb.Ado.RollbackTran();
            throw Oops.Oh($"异常：{e.Message}");

        }
        #endregion

    }

    /// <summary>
    /// 查询下拉框内容
    /// </summary>
    /// <returns></returns>
    [HttpPost("/SelectMeInventoryOptions")]
    public Dictionary<string, List<MeInventoryOptions>> SelectMeInventoryOptions() {

        //使用部门
        List<MeInventoryOptions> meInventorie1 =  _SjDb.Queryable<MeInventoryOptions>().Where(a => a.OPTIONS_TYPE == 1 ).ToList();

        //厂商
        List<MeInventoryOptions> meInventorie2 = _SjDb.Queryable<MeInventoryOptions>().Where(a => a.OPTIONS_TYPE == 2).ToList();


        Dictionary<string, List<MeInventoryOptions>> objects = new Dictionary<string, List<MeInventoryOptions>>();

        objects.Add("USEDEPARTMENT", meInventorie1);
        objects.Add("MANUFACTURERS", meInventorie2);

        return objects;
    }



    /// <summary>
    /// 短信发送
    /// </summary>
    /// <param name="phoneNumber">手机号码</param>
    /// <returns></returns>
    [HttpPost("/Inventory/SendSms")]
    [AllowAnonymous]
    [SuppressMonitor]
    public bool SendSmsTY()
    {
        var LoginRoles = _Dilon.Queryable<SysUserRole>()
               .LeftJoin<SysUser>((r, u) => (r.UserId == u.Id))
                     .Where((r, u) => r.RoleId.ToString() == "14951643225669")
                    .Select((r, u) => new SalesmanRole2
                    {
                        USERID = u.Account,
                        USERNAME = u.RealName,
                        ROLEID = r.RoleId.ToString(),
                        PHONE = u.Phone
                    }).ToList();


        IClientProfile profile = DefaultProfile.GetProfile("cn-hangzhou", "LTAI5tDCKnkiQTBDvHQGe79S", "oMIN2Lgg8eMv20RWMHFaxni1IkGHQm");
        DefaultAcsClient client = new DefaultAcsClient(profile);
        List<InventoryForewarn> forewarns = SelectInsufficientInventoryList();
        string SMSName = "SMS_463945145";//短信模板编号



        if (SelectIsHaveSMS(SMSName)) {

            foreach (SalesmanRole2 user in LoginRoles)
            {

                    SendSmsRequest request = new SendSmsRequest();
                    request.PhoneNumbers = user.PHONE;
                    request.SignName = "生久科技";// 短信签名，需要在阿里云控制台申请

                    request.TemplateCode = SMSName;
                    // request.TemplateParam ="{\"partnumber\":\"123\",\"partsname\":\"456\",\"specificationtype\":\"789\"}";

                    request.TemplateParam = $"{{\"partcount\":\"{forewarns.Count}\"}}";



                    try
                    {
                        //发送短信
                        SendSmsResponse response = client.GetAcsResponse(request);

                    }
                    catch (ServerException ex)
                    {
                        throw Oops.Oh($"异常：{ex.Message}");
                        // 处理异常
                    }
                    catch (ClientException ex)
                    {
                        throw Oops.Oh($"异常：{ex.Message}");
                        // 处理异常
                    }


    
            }

          

            #region 插入事务
            try
            {
                //开启事务
                _SjDb.Ado.BeginTran();
                int cnt =  _SjDb.Insertable<SMSRecord>(new SMSRecord() {

                SMSName = SMSName,
                SendTime = DateTime.Now.Date,
                SendMessage ="发送成功"

                }).ExecuteCommand();

                if (cnt == 0)
                {
                    throw Oops.Oh($"新建任务失败！");
                }
                //提交事务
                _SjDb.Ado.CommitTran();
                return true;
               
            }
            catch (Exception e)
            {
                //回滚事务
                _SjDb.Ado.RollbackTran();
                throw Oops.Oh($"异常：{e.Message}");

            }
            #endregion
        }






        return false;

      
    }


    /// <summary>
    /// 查询库存不足的数据
    /// </summary>
    /// <returns></returns>
    [HttpPost("/Inventory/SelectInsufficientInventory")]
    [AllowAnonymous]
    [SuppressMonitor]
    public List<InventoryForewarn> SelectInsufficientInventoryList() {

        var output =  _SjDb.Queryable<InventoryForewarn>()
           .OrderBy(l => l.CREATETIME, OrderByType.Desc) //创建日期排序
           .ToList();

        HashSet<string> partNumberList = new HashSet<string>();

        foreach (var item in output)
        {
            partNumberList.Add(item.PARTNUMBER);
        }

        var imfaList = _T100Db.Queryable<ImaaT>()
       .LeftJoin<InagT>((a, o) => a.Imaaent == o.Inagent && a.Imaa001 == o.Inag001 && o.Inag004 == "81" && o.Inagsite == "SJGS" && o.Inag008 != 0)
       .Where(a => a.Imaaud003 == "SJGS")
       .Where(a => a.Imaastus == "Y")
       .Where(a => partNumberList.Contains(a.Imaa001))
       .Select((a, o) => new InventoryNumber()
       {
           PARTNUMBER = a.Imaa001,
           NOWINVENTORY = o.Inag009 == null ? 0 : Convert.ToInt32(o.Inag009)
       })
       .ToList();

        List<InventoryForewarn> list = new List<InventoryForewarn>();

        foreach (var inventoryForewarn in output)
        {

            foreach (var item in imfaList)
            {
                if (inventoryForewarn.PARTNUMBER.Equals(item.PARTNUMBER) && inventoryForewarn.SAFEINVENTORY>item.NOWINVENTORY) {
                    list.Add(new InventoryForewarn() {

                        ID = inventoryForewarn.ID,
                        PARTNUMBER = inventoryForewarn.PARTNUMBER,
                        SPECIFICATIONTYPE = inventoryForewarn.SPECIFICATIONTYPE,
                        USEDEPARTMENT = inventoryForewarn.USEDEPARTMENT,
                        SAFEINVENTORY = inventoryForewarn.SAFEINVENTORY,
                        MAXIMUMINVENTORY = inventoryForewarn.MAXIMUMINVENTORY,
                        NOWINVENTORY = item.NOWINVENTORY,
                        EQUIPMENTAMOUNT = inventoryForewarn.EQUIPMENTAMOUNT,
                        ESTIMATEAMOUNT = inventoryForewarn.ESTIMATEAMOUNT,
                        PROCUREMENTAMOUNT = (inventoryForewarn.SAFEINVENTORY - item.NOWINVENTORY) * inventoryForewarn.ESTIMATEAMOUNT,
                        PROCUREMENTCYCLE = inventoryForewarn.PROCUREMENTCYCLE,
                        USAGECYCLE = inventoryForewarn.USAGECYCLE,
                        MANUFACTURERS = inventoryForewarn.MANUFACTURERS,
                        INVENTORYREMARK = inventoryForewarn.INVENTORYREMARK,
                        CREATETIME = inventoryForewarn.CREATETIME,


                    });
                }

            }
        }




        return list;
    }


    /// <summary>
    /// 查询是否发送过短信
    /// </summary>
    /// <returns></returns>
    private bool SelectIsHaveSMS(string SMSName)
    {

        int output = _SjDb.Queryable<SMSRecord>()  
           .Where((l) => (l.SMSName == SMSName))//品号
           .Where((l) => l.SendTime == DateTime.Now.Date)//时间为当天
          .Count();

        return output==0?true:false;
    }


    /// <summary>
    /// 删除ME库存信息
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    [HttpPost("/DeleteInventoryForewarnMessage")]
    public async Task<string> DeleteInventoryForewarnMessage(int id) {

        var LoginRoles = _Dilon.Queryable<SysUserRole>()
            .LeftJoin<SysUser>((r, u) => (r.UserId == u.Id))
                  .Where((r, u) => r.UserId.ToString() == _User.ID)
                 .Select((r, u) => new SalesmanRole
                 {
                     USERID = u.Account,
                     USERNAME = u.RealName,
                     ROLEID = r.RoleId.ToString()
                 }).ToList();

        int role = LoginRoles.Where(u => u.ROLEID == "14930050012997").Count();//判断是否拥有ME库存管理员角色

        if (role == 0)
        {
            throw Oops.Oh($"您这边没有权限！");

        }


        //提交
        try
            {
                _SjDb.Ado.BeginTran();
              int count = await _SjDb.Deleteable<InventoryForewarn>().In(id).ExecuteCommandAsync();



                if (count != 1)
                {
                    throw Oops.Oh("删除失败！");
                }
                _SjDb.Ado.CommitTran();
                return "删除成功！";
            }
            catch (Exception e)
            {
                _SjDb.Ado.RollbackTran();
                throw Oops.Oh($"异常：{e.Message}");
            }
        

    }
}
